﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spPostDeploy_LookupTable_SqlVersions]
#-- Purpose:		To be called by a process after the TFS Build has completed.
#--					Updates the tblScripts lookup table to be the latest values.
#--	Last Update:	02/13/2017
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spPostDeploy_LookupTable_SqlVersions]
AS

SET NOCOUNT ON

--- Build the list of versions
DECLARE @t TABLE (ver_major_ro int, ver_minor_ro int, ver_description varchar(20), ver_current_sp_version varchar(20), ver_current_version varchar(20))
INSERT INTO @t (ver_major_ro, ver_minor_ro, ver_description, ver_current_sp_version, ver_current_version)
	SELECT	8, 0, 'SQL Server 2000', '8.00.2039', '8.00.2305'
	UNION SELECT 9, 0, 'SQL Server 2005', '9.00.5000.00', '9.00.5324.00'
	UNION SELECT 10, 0, 'SQL Server 2008', '10.0.5500.0', '10.0.6547.0'
	UNION SELECT 10, 5, 'SQL Server 2008 R2', '10.0.6000.0', '10.0.6542.0'
	UNION SELECT 10, 50, 'SQL Server 2008 R2', '10.50.6000.0', '10.50.6542.0'
	UNION SELECT 11, 0, 'SQL Server 2012', '11.0.6020.0', '11.0.6579.0'
	UNION SELECT 12, 0, 'SQL Server 2014', '12.0.5000.0', '12.0.5538.0'
	UNION SELECT 13, 0, 'SQL Server 2016', '13.0.4001.0', '13.0.4411.0'

--- Update Changed Records
UPDATE		s
SET			ver_description = t.ver_description,
			ver_current_sp_version = t.ver_current_sp_version,
			ver_current_version = t.ver_current_version
FROM		@t t
JOIN		[dbo].[tblMSX_sql_version] s ON t.ver_major_ro = s.ver_major_ro
			AND t.ver_minor_ro = s.ver_minor_ro
			AND NOT (
				t.ver_description = s.ver_description
				AND ISNULL(t.ver_current_sp_version, '') = ISNULL(s.ver_current_sp_version, '')
				AND ISNULL(t.ver_current_version, '') = ISNULL(s.ver_current_version, '')
			)

--- Insert Missing Records
INSERT INTO [dbo].[tblMSX_sql_version] (ver_major_ro, ver_minor_ro, ver_description, ver_current_sp_version, ver_current_version)
	SELECT		t.ver_major_ro,
				t.ver_minor_ro,
				t.ver_description,
				t.ver_current_sp_version,
				t.ver_current_version
	FROM		@t t
	LEFT JOIN	[dbo].[tblMSX_sql_version] s ON t.ver_major_ro = s.ver_major_ro
				AND t.ver_minor_ro = s.ver_minor_ro
	WHERE		s.ver_major_ro IS NULL

SET NOCOUNT OFF